package com.cpi.cornpp.biweeks.view;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.cpi.cornpp.biweek.exception.BiWeekException;
import com.cpi.cornpp.biweeks.dto.BiWeekPeriodVersionDTO;
import com.cpi.cornpp.common.exception.CornPPException;
import com.cs.common.pool.DBDAO;
import com.cs.common.pool.exception.PoolException;

public class BiWeekPeriodVersionDAO extends DBDAO {
	public static final String SQL_SELECT_BI_WEEK_PERIOD_VERSION = "SELECT biweek_period_version_id, biweek_period_id, version_id FROM biweeks_periods_versions";
	
	public List getBiWeekPeriodVersionsByVersionId(Integer versionId) throws BiWeekException{
		List biWeekPeriodVersions = null;
		StringBuffer sql = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		
		biWeekPeriodVersions = new ArrayList(0);
		
		try{
			
			if (versionId != null){
				sql = new StringBuffer(SQL_SELECT_BI_WEEK_PERIOD_VERSION);
				
				sql.append(SQL_WHERE);
				sql.append(BiWeekPeriodVersionDTO.BI_WEEK_PERIOD_VERSION_VERSION_ID);
				sql.append(PREPARED_STATEMENT_PARAMETER_TOKEN_ASSIGNMENT);
				
				pstm = prepareSQL(sql.toString(), dsName);
				
				pstm.setInt(1, versionId.intValue());
				
				rs = executeQuery(pstm);
				
				biWeekPeriodVersions = mapRows(rs);
			}
			
		} catch(PoolException poe){
			BiWeekException bwe = null;
			poe.printStackTrace(System.out);
			bwe = new BiWeekException(poe, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw bwe;
		} catch(SQLException sqle){
			BiWeekException bwe = null;
			sqle.printStackTrace(System.out);
			bwe = new BiWeekException(sqle, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw bwe;
		}finally {
			try{
				closeConnection(pstm.getConnection());
			}catch(Exception exception){
			}
		}
		
		return biWeekPeriodVersions;
	}
	
	public BiWeekPeriodVersionDTO getBiWeekVersionById(Integer biWeekVersionId) throws BiWeekException{
		BiWeekPeriodVersionDTO biWeekPeriodVersionDTO = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		StringBuffer sql = null;
		
		try{
			
			if (biWeekVersionId != null){
				sql = new StringBuffer(SQL_SELECT_BI_WEEK_PERIOD_VERSION);
				
				sql.append(SQL_WHERE);
				sql.append(BiWeekPeriodVersionDTO.BI_WEEK_PERIOD_VERSION_ID);
				sql.append(PREPARED_STATEMENT_PARAMETER_TOKEN_ASSIGNMENT);
				
				pstm = prepareSQL(sql.toString(), dsName);
				
				pstm.setInt(1, biWeekVersionId.intValue());
				
				rs = executeQuery(pstm);
				biWeekPeriodVersionDTO = mapRow(rs, 1);
			}
			
		} catch(PoolException poe){
			BiWeekException bwe = null;
			poe.printStackTrace(System.out);
			bwe = new BiWeekException(poe, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw bwe;
		} catch(SQLException sqle){
			BiWeekException bwe = null;
			sqle.printStackTrace(System.out);
			bwe = new BiWeekException(sqle, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw bwe;
		}finally {
			try{
				closeConnection(pstm.getConnection());
			}catch(Exception exception){
			}
		}
		
		return biWeekPeriodVersionDTO;
	}
	
	private BiWeekPeriodVersionDTO mapRow(ResultSet rs, int index) throws SQLException{
		BiWeekPeriodVersionDTO dto = null;
		
		if ((rs != null) && (rs.absolute(index))){
			dto = new BiWeekPeriodVersionDTO();
			
			dto.setBiWeekPeriodVersionId(new Integer(rs.getInt(BiWeekPeriodVersionDTO.BI_WEEK_PERIOD_VERSION_ID)));
			dto.setBiWeekPeriodId(new Integer(rs.getInt(BiWeekPeriodVersionDTO.BI_WEEK_PERIOD_ID)));
			dto.setVersionId(new Integer(rs.getInt(BiWeekPeriodVersionDTO.BI_WEEK_PERIOD_VERSION_VERSION_ID)));
		}
		
		return dto;
	}
	
	private List mapRows(ResultSet rs) throws SQLException{
		List dtoList = null;
		
		dtoList = new ArrayList(0);
		
		if (rs != null){
			
			for (; rs.next(); ){
				dtoList.add(mapRow(rs, rs.getRow()));
			}
		}
		
		return dtoList;
	}
}